{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MiniDataAPI Spec"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `MiniDataAPI` is a persistence API specification that designed to be small and relatively easy to implement across a wide range of datastores. While early implementations have been SQL-based, the specification can be quickly implemented in key/value stores, document databases, and more."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "::: {.callout-warning title=\"Work in Progress\"}\n",
    "\n",
    "The MiniData API spec is a work in progress, subject to change. While the majority of design is complete, expect there could be breaking changes.\n",
    ":::"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Why?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The MiniDataAPI specification allows us to use the same API for many different database engines. Any application using the MiniDataAPI spec for interacting with its database requires no modification beyond import and configuration changes to switch database engines. For example, to convert an application from Fastlite running SQLite to FastSQL running PostgreSQL, should require only changing these two lines:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "::: {.columns }\n",
    "::: {.column width=\"19%\" style=\"border-right: 1px solid #ccc; padding-right: 10px;\"}\n",
    "FastLite version\n",
    "```python\n",
    "from fastlite import *\n",
    "db = database('test.db')\n",
    "```\n",
    ":::\n",
    "\n",
    "::: {.column width=\"79%\" style=\"padding-left: 10px;\"}\n",
    "FastSQL version\n",
    "```python\n",
    "from fastsql import *\n",
    "db = Database('postgres:...')\n",
    "```\n",
    ":::\n",
    ":::"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As both libraries adhere to the MiniDataAPI specification, the rest of the code in the application should remain the same. The advantage of the MiniDataAPI spec is that it allows people to use whatever datastores they have access to or prefer.\n",
    "\n",
    ":::{.callout-note}\n",
    "\n",
    "Switching databases won't migrate any existing data between databases.\n",
    ":::"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Easy to learn, quick to implement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The MiniDataAPI specification is designed to be easy-to-learn and quick to implement. It focuses on straightforward Create, Read, Update, and Delete (CRUD) operations.\n",
    "\n",
    "MiniDataAPI databases aren't limited to just row-based systems. In fact, the specification is closer in design to a key/value store than a set of records. What's exciting about this is we can write implementations for tools like Python dict stored as JSON, Redis, and even the venerable ZODB."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Limitations of the MiniDataAPI Specification"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> \"Mini refers to the lightweightness of specification, not the data.\"\n",
    "> \n",
    "> -- Jeremy Howard\n",
    "\n",
    "The advantages of the MiniDataAPI come at a cost. The MiniDataAPI specification focuses a very small set of features compared to what can be found in full-fledged ORMs and query languages. It intentionally avoids nuances or sophisticated features. \n",
    "\n",
    "This means the specification does not include joins or formal foreign keys. Complex data stored over multiple tables that require joins isn't handled well. For this kind of scenario it's probably for the best to use more sophisticated ORMs or even direct database queries."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary of the MiniDataAPI Design"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Easy-to-learn\n",
    "- Relative quick to implement for new database engines\n",
    "- An API for CRUD operations\n",
    "- For many different types of databases including row- and key/value-based designs\n",
    "- Intentionally small in terms of features: no joins, no foreign keys, no database specific features\n",
    "- Best for simpler designs, complex architectures will need more sophisticated tools. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#| hide\n",
    "from fasthtml.common import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect/construct the database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We connect or construct the database by passing in a string connecting to the database endpoint or a filepath representing the database's location. While this example is for SQLite running in memory, other databases such as PostgreSQL, Redis, MongoDB, might instead use a URI pointing at the database's filepath or endpoint. The method of connecting to a DB is *not* part of this API, but part of the underlying library. For instance, for fastlite:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = database(':memory:')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here's a complete list of the available methods in the API, all documented below (assuming `db` is a database and `t` is a table):\n",
    "\n",
    "- `db.create`\n",
    "- `t.insert`\n",
    "- `t.delete`\n",
    "- `t.update`\n",
    "- `t[key]`\n",
    "- `t(...)`\n",
    "- `t.xtra`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the sake of expediency, this document uses a SQL example. However, tables can represent anything, not just the fundamental construct of a SQL databases. They might represent keys within a key/value structure or files on a hard-drive."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "We use a `create()` method attached to `Database` object (`db` in our example) to create the tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Table user (name, email, year_started)>"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "class User: name:str; email: str; year_started:int\n",
    "users = db.create(User, pk='name')\n",
    "users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Table user (name, email, year_started)>"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "class User: name:str; email: str; year_started:int\n",
    "users = db.create(User, pk='name')\n",
    "users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If no `pk` is provided, `id` is assumed to be the primary key. Regardless of whether you mark a class as a dataclass or not, it will be turned into one -- specifically into a [`flexiclass`](https://fastcore.fast.ai/xtras.html#flexiclass)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Table todo (id, title, detail, status, name)>"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "@dataclass\n",
    "class Todo: id: int; title: str; detail: str; status: str; name: str\n",
    "todos = db.create(Todo) \n",
    "todos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compound primary keys"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The MiniData API spec supports compound primary keys, where more than one column is used to identify records. We'll also use this example to demonstrate creating a table using a dict of keyword arguments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Publication: authors: str; year: int; title: str\n",
    "publications = db.create(Publication, pk=('authors', 'year')) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transforming tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Depending on the database type, this method can include transforms - the ability to modify the tables. Let's go ahead and add a password field for our table called `pwd`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Table user (name, email, year_started, pwd)>"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "class User: name:str; email: str; year_started:int; pwd:str\n",
    "users = db.create(User, pk='name', transform=True)\n",
    "users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Manipulating data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The specification is designed to provide as straightforward CRUD API (Create, Read, Update, and Delete) as possible. Additional features like joins are out of scope."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### .insert()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add a new record to the database. We want to support as many types as possible, for now we have tests for Python classes, dataclasses, and dicts. Returns an instance of the new record.\n",
    "\n",
    "Here's how to add a record using a Python class:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "User(name='Braden', email='b@example.com', year_started=2018, pwd=None)"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.insert(User(name='Braden', email='b@example.com', year_started=2018))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use keyword arguments directly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "User(name='Alma', email='a@example.com', year_started=2019, pwd=None)"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.insert(name='Alma', email='a@example.com', year_started=2019)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now Charlie gets added via a Python dict."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "User(name='Charlie', email='c@example.com', year_started=2018, pwd=None)"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.insert({'name': 'Charlie', 'email': 'c@example.com', 'year_started': 2018})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now TODOs. Note that the inserted row is returned:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Todo(id=3, title='Finish development of FastHTML', detail=None, status='closed', name='Charlie')"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "todos.insert(Todo(title='Write MiniDataAPI spec', status='open', name='Braden'))\n",
    "todos.insert(title='Implement SSE in FastHTML', status='open', name='Alma')\n",
    "todo = todos.insert(dict(title='Finish development of FastHTML', status='closed', name='Charlie'))\n",
    "todo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's do the same with the `Publications` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Publication(authors='Alma', year=2035, title='FastHTML, the early years')"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "publications.insert(Publication(authors='Alma', year=2019, title='FastHTML'))\n",
    "publications.insert(authors='Alma', year=2030, title='FastHTML and beyond')\n",
    "publication= publications.insert((dict(authors='Alma', year=2035, title='FastHTML, the early years')))\n",
    "publication"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Square bracket search []"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get a single record by entering a primary key into a table object within square brackets. Let's see if we can find Alma."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "User(name='Alma', email='a@example.com', year_started=2019, pwd=None)"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user = users['Alma']\n",
    "user"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If no record is found, a `NotFoundError` error is raised. Here we look for David, who hasn't yet been added to our users table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User not found\n"
     ]
    }
   ],
   "source": [
    "try: users['David']\n",
    "except NotFoundError: print(f'User not found')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here's a demonstration of a ticket search, demonstrating how this works with non-string primary keys. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Todo(id=1, title='Write MiniDataAPI spec', detail=None, status='open', name='Braden')"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "todos[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compound primary keys can be supplied in lists or tuples, in the order they were defined. In this case it is the `authors` and `year` columns.\n",
    "\n",
    "Here's a query by compound primary key done with a `list`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Publication(authors='Alma', year=2019, title='FastHTML')"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "publications[['Alma', 2019]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here's the same query done directly with index args."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Publication(authors='Alma', year=2030, title='FastHTML and beyond')"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "publications['Alma', 2030]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parentheses search ()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get zero to many records by entering values with parentheses searches. If nothing is in the parentheses, then everything is returned. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[User(name='Braden', email='b@example.com', year_started=2018, pwd=None),\n",
       " User(name='Alma', email='a@example.com', year_started=2019, pwd=None),\n",
       " User(name='Charlie', email='c@example.com', year_started=2018, pwd=None)]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can order the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[User(name='Alma', email='a@example.com', year_started=2019, pwd=None),\n",
       " User(name='Braden', email='b@example.com', year_started=2018, pwd=None),\n",
       " User(name='Charlie', email='c@example.com', year_started=2018, pwd=None)]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users(order_by='name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can filter on the results:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[User(name='Alma', email='a@example.com', year_started=2019, pwd=None)]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users(where=\"name='Alma'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Generally you probably want to use placeholders, to avoid SQL injection attacks:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[User(name='Alma', email='a@example.com', year_started=2019, pwd=None)]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users(\"name=?\", ('Alma',))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can limit results with the `limit` keyword:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[User(name='Braden', email='b@example.com', year_started=2018, pwd=None)]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users(limit=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we're using the `limit` keyword, we can also use the `offset` keyword to start the query later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[User(name='Alma', email='a@example.com', year_started=2019, pwd=None),\n",
       " User(name='Charlie', email='c@example.com', year_started=2018, pwd=None)]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users(limit=5, offset=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### .update()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Update an existing record of the database. Must accept Python dict, dataclasses, and standard classes. Uses the primary key for identifying the record to be changed. Returns an instance of the updated record. \n",
    "\n",
    "Here's with a normal Python class:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "User(name='Alma', email='a@example.com', year_started=2019, pwd=None)"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "User(name='Alma', email='a@example.com', year_started=2099, pwd=None)"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user.year_started = 2099\n",
    "users.update(user)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or use a dict:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "User(name='Alma', email='a@example.com', year_started=2199, pwd=None)"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.update(dict(name='Alma', year_started=2199, email='a@example.com'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or use kwargs:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "User(name='Alma', email='a@example.com', year_started=2149, pwd=None)"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.update(name='Alma', year_started=2149)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the primary key doesn't match a record, raise a `NotFoundError`. \n",
    "\n",
    "John hasn't started with us yet so doesn't get the chance yet to travel in time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User not found\n"
     ]
    }
   ],
   "source": [
    "try: users.update(User(name='John', year_started=2024, email='j@example.com'))\n",
    "except NotFoundError: print('User not found')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### .delete()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Delete a record of the database. Uses the primary key for identifying the record to be removed. Returns a table object.\n",
    "\n",
    "Charlie decides to not travel in time. He exits our little group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Table user (name, email, year_started, pwd)>"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.delete('Charlie')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the primary key value can't be found, raises a `NotFoundError`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User not found\n"
     ]
    }
   ],
   "source": [
    "try: users.delete('Charlies')\n",
    "except NotFoundError: print('User not found')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In John's case, he isn't time travelling with us yet so can't be removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User not found\n"
     ]
    }
   ],
   "source": [
    "try: users.delete('John')\n",
    "except NotFoundError: print('User not found')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Deleting records with compound primary keys requires providing the entire key. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Table publication (authors, year, title)>"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "publications.delete(['Alma' , 2035])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `in` keyword"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Are `Alma` and `John` contained `in` the Users table? Or, to be technically precise, is the item with the specified primary key value `in` this table? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(True, False)"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'Alma' in users, 'John' in users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also works with compound primary keys, as shown below. You'll note that the operation can be done with either a `list` or `tuple`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "['Alma', 2019] in  publications"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now for a `False` result, where John has no publications."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "('John', 1967) in publications"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### .xtra()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we set fields within the `.xtra` function to a particular value, then indexing is also filtered by those. This applies to every database method except for record creation. This makes it easier to limit users (or other objects) access to only things for which they have permission. This is a one-way operation, once set it can't be undone for a particular table object.\n",
    "\n",
    "For example, if we query all our records below without setting values via the `.xtra` function, we can see todos for everyone. Pay special attention to the `id` values of all three records, as we are about to filter most of them away."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Todo(id=1, title='Write MiniDataAPI spec', detail=None, status='open', name='Braden'),\n",
       " Todo(id=2, title='Implement SSE in FastHTML', detail=None, status='open', name='Alma'),\n",
       " Todo(id=3, title='Finish development of FastHTML', detail=None, status='closed', name='Charlie')]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "todos()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's use `.xtra` to constrain results just to Charlie. We set the `name` field in Todos, but it could be any field defined for this table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "todos.xtra(name='Charlie')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've now set a field to a value with `.xtra`, if we loop over all the records again, only those assigned to records with a `name` of `Charlie` will be displayed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Todo(id=3, title='Finish development of FastHTML', detail=None, status='closed', name='Charlie')]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "todos()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `in` keyword is also affected. Only records with a `name` of Charlie will evaluate to be `True`. Let's demonstrate by testing it with a Charlie record:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Todo(id=3, title='Finish development of FastHTML', detail=None, status='closed', name='Charlie')"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ct = todos[3]\n",
    "ct"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Charlie's record has an ID of 3. Here we demonstrate that Charlie's TODO can be found in the list of todos:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ct.id in todos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we try `in` with the other IDs the query fails because the filtering is now set to just records with a name of Charlie."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(False, False)"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "1 in todos, 2 in todos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Record not found\n"
     ]
    }
   ],
   "source": [
    "try: todos[2]\n",
    "except NotFoundError: print('Record not found')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are also constrained by what records we can update. In the following example we try to update a TODO not named 'Charlie'. Because the name is wrong, the `.update` function will raise a `NotFoundError`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Record not updated\n"
     ]
    }
   ],
   "source": [
    "try: todos.update(Todo(id=1, title='Finish MiniDataAPI Spec', status='closed', name='Braden'))\n",
    "except NotFoundError as e: print('Record not updated')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unlike poor Braden, Charlie isn't filtered out. Let's update his TODO."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Todo(id=3, title='Finish development of FastHTML', detail=None, status='closed', name='Charlie')"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "todos.update(Todo(id=3, title='Finish development of FastHTML', detail=None, status='closed', name='Charlie'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, once constrained by `.xtra`, only records with Charlie as the name can be deleted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Record not updated\n"
     ]
    }
   ],
   "source": [
    "try: todos.delete(1)\n",
    "except NotFoundError as e: print('Record not updated')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Charlie's TODO was to finish development of FastHTML. While the framework will stabilize, like any good project it will see new features added and the odd bug corrected for many years to come. Therefore, Charlie's TODO is nonsensical. Let's delete it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Table todo (id, title, detail, status, name)>"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "todos.delete(ct.id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When a TODO is inserted, the `xtra` fields are automatically set. This ensures that we don't accidentally, for instance, insert items for others users. Note that here we don't set the `name` field, but it's still included in the resultant row:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Todo(id=3, title='Rewrite personal site in FastHTML', detail=None, status='open', name='Charlie')"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ct = todos.insert(Todo(title='Rewrite personal site in FastHTML', status='open'))\n",
    "ct"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we try to change the username to someone else, the change is ignored, due to `xtra`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Todo(id=3, title='Rewrite personal site in FastHTML', detail=None, status='open', name='Charlie')"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ct.name = 'Braden'\n",
    "todos.update(ct)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL-first design"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = None\n",
    "User = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Table user (name, email, year_started, pwd)>"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users = db.t.user\n",
    "users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(This section needs to be documented properly.)\n",
    "\n",
    "From the table objects we can extract a Dataclass version of our tables. Usually this is given an singular uppercase version of our table name, which in this case is `User`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "User = users.dataclass()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "User(name='Braden', email='b@example.com', year_started=2018, pwd=UNSET)"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "User(name='Braden', email='b@example.com', year_started=2018)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Implementations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Implementing MiniDataAPI for a new datastore"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "For creating new implementations, the code examples in this specification are the test case for the API. New implementations should pass the tests in order to be compliant with the specification.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Implementations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- [fastlite](https://github.com/AnswerDotAI/fastlite) - The original implementation, only for Sqlite\n",
    "- [fastsql](https://github.com/AnswerDotAI/fastsql) - An SQL database agnostic implementation based on the excellent SQLAlchemy library."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "python3",
   "language": "python",
   "name": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
